﻿using ET.DBCommon.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ET.DBCommon.DAL
{
    public class ExamresultsTestQuestionTaskStepDAL
    {
        internal static ObservableCollection<ExamresultsTestQuestionTaskStepModel> GetStepByTaskID(int taskID)
        {
            string sql = "SELECT * FROM examresultstestquestiontaskstep where ExamResultsTestQuestionTaskID = @ExamResultsTestQuestionTaskID order by `Order`";

            MySqlParameter[] parms = { new MySqlParameter("@ExamResultsTestQuestionTaskID", taskID) };
            DataSet ds = DbHelper.ExecuteDataset(CommandType.Text, sql, parms);

            ObservableCollection<ExamresultsTestQuestionTaskStepModel> list = new ObservableCollection<ExamresultsTestQuestionTaskStepModel>();

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    list.Add(GetExamresultsTestQuestionTaskStepModel(dt.Rows[i]));
                }
            }
            return list;
        }

        public static byte[] GetStepAudioBuff(int stepID)
        {
            string sql = @"SELECT 
                                c.AudioFile
                            FROM
                                examresultstestquestiontaskstep a
                                    INNER JOIN
                                examresultslog b ON a.ID = b.ExamSetpID
                                    AND b.OperationLogID > 0
                                    INNER JOIN
                                operationlog c ON b.OperationLogID = c.ID
                                    AND c.AudioFile IS NOT NULL
                          where a.ID =" + stepID;
            DataSet ds = DbHelper.ExecuteDataset(sql);

            if (ds != null && ds.Tables[0].Rows.Count > 0)
                return (byte[])ds.Tables[0].Rows[0]["AudioFile"];

            //string sql = @"SELECT b.OperationLogID FROM
            //                examresultstestquestiontaskstep a
            //                    INNER JOIN
            //                examresultslog b ON a.ID = b.ExamSetpID AND b.OperationLogID > 0
            //              where a.ID =" + stepID;

            //DataSet ds = DbHelper.ExecuteDataset(sql);

            //if (ds != null && ds.Tables[0].Rows.Count > 0)
            //{
            //    sql = "SELECT  c.AudioFile FROM  operationlog c where c.ID="+ ds.Tables[0].Rows[0]["OperationLogID"].ToString() + "   AND c.AudioFile IS NOT NULL";
            //    ds = DbHelperLog.ExecuteDataset(sql);

            //    if (ds != null && ds.Tables[0].Rows.Count > 0)
            //        return (byte[])ds.Tables[0].Rows[0]["AudioFile"];
            //}
            return null;
        }
        /// <summary>
        /// 根据当前步骤ID获取试卷的任务
        /// </summary>
        /// <param name="stepID">步骤ID</param>
        /// <returns></returns>
        public static ExamresultsTestQuestionTaskStepModel GetStepByID(int stepID)
        {
            string sql = "SELECT * FROM examresultstestquestiontaskstep where ID =" + stepID;

            DataSet ds = DbHelper.ExecuteDataset(sql);

            ExamresultsTestQuestionTaskStepModel info = null;

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    info = GetExamresultsTestQuestionTaskStepModel(dt.Rows[i]);
                }
            }
            return info;
        }

        public static bool InsertStep(out int newId, ExamresultsTestQuestionTaskStepModel testQuestionTaskStep)
        {
            string sql = @"INSERT INTO `examresultstestquestiontaskstep`
                                (`ExamresultsTestQuestionTaskID`,
                                `StepID`,
                                `Parameter`,
                                `Score`,
                                `SystemType`,
                                `TaskType`,
                                `StepDescribe`,
                                `DevType`,
                                `DevID`,
                                `ScoreStandard`,
                                `OperationScore`,
                                `ResultScore`,
                                `TimeScore`,
                                `TotalScore`,
                                `CompositeScore`,
                                `StepContent`,
                                `Prerequisite`,
                                `Keyword`,
                                `order`,
                                `IsRepeat`,
                                `TestQuestionTaskStepID`,
                                `IsCommand`,
                                `OutRangeScore`,
                                LimitedDuration,
                                Duration,
                                Deduction,
                                `IsKeyStep`,
                                `StepID1`,
                                `StepDescribe1`,
                                `DevType1`,
                                `DevID1`,
                                `Parameter1`,
                                 `Estimate`)
                            VALUES(@ExamresultsTestQuestionTaskID,@StepID,@Parameter,@Score,@SystemType,@TaskType,@StepDescribe,
                            @DevType,@DevID,@ScoreStandard,0,0,0,0,0,@StepContent,@Prerequisite,@Keyword,@Order,@IsRepeat,
                            @TestQuestionTaskStepID,@IsCommand,@OutRangeScore,@LimitedDuration,@Duration,@Deduction,@IsKeyStep
,@StepID1,@StepDescribe1,@DevType1,@DevID1,@Parameter1,@Estimate)";
            MySqlParameter[] parms = {
                new MySqlParameter("@ExamresultsTestQuestionTaskID", testQuestionTaskStep.ExamresultsTestQuestionTaskID ),
                new MySqlParameter("@StepID", testQuestionTaskStep.StepID),
                new MySqlParameter("@Parameter", testQuestionTaskStep.Parameter),
                new MySqlParameter("@Score", testQuestionTaskStep.Score),

                new MySqlParameter("@SystemType", testQuestionTaskStep.SystemType ),
                new MySqlParameter("@TaskType", testQuestionTaskStep.TaskType),
                new MySqlParameter("@StepDescribe", testQuestionTaskStep.StepDescribe),
                new MySqlParameter("@DevType", testQuestionTaskStep.DevType),
                new MySqlParameter("@DevID", testQuestionTaskStep.DevID ),
                new MySqlParameter("@ScoreStandard", testQuestionTaskStep.ScoreStandard),
                new MySqlParameter("@StepContent", testQuestionTaskStep.StepContent),
                new MySqlParameter("@Prerequisite", testQuestionTaskStep.Prerequisite),
                new MySqlParameter("@Keyword", testQuestionTaskStep.Keyword),
                new MySqlParameter("@Order", testQuestionTaskStep.Order),
                new MySqlParameter("@IsRepeat", testQuestionTaskStep.IsRepeat),
                new MySqlParameter("@IsCommand", testQuestionTaskStep.IsCommand),
                new MySqlParameter("@TestQuestionTaskStepID", testQuestionTaskStep.TestQuestionTaskStepID),
                new MySqlParameter("@OutRangeScore", testQuestionTaskStep.OutRangeScore),

                new MySqlParameter("@LimitedDuration", testQuestionTaskStep.LimitedDuration),
                new MySqlParameter("@Duration", testQuestionTaskStep.Duration),
                new MySqlParameter("@Deduction", testQuestionTaskStep.Deduction),
                new MySqlParameter("@IsKeyStep", testQuestionTaskStep.IsKeyStep),
                  new MySqlParameter("@StepID1", testQuestionTaskStep.StepID1),
                new MySqlParameter("@StepDescribe1", testQuestionTaskStep.StepDescribe1),

                new MySqlParameter("@DevType1", testQuestionTaskStep.DevType1),
                new MySqlParameter("@DevID1", testQuestionTaskStep.DevID1),
                new MySqlParameter("@Parameter1", testQuestionTaskStep.Parameter1),
                new MySqlParameter("@Estimate", testQuestionTaskStep.Estimate),
            };

            return DbHelper.ExecuteNonQuery(out newId, sql, parms) > 0;
        }

        public static bool UpdateMutexStep(ExamresultsTestQuestionTaskStepModel testQuestionTaskStep)
        {
            string sql = @"UPDATE `ExamresultsTestQuestionTaskStep`
                            SET
                            MutexStep=@MutexStep
                             WHERE `ID` = @ID ";

            MySqlParameter[] parms = {
                new MySqlParameter("@ID", testQuestionTaskStep.ID),
                new MySqlParameter("@MutexStep", testQuestionTaskStep.MutexStep),
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        internal static ExamresultsTestQuestionTaskStepModel GetExamresultsTestQuestionTaskStepModel(DataRow dr)
        {
            ExamresultsTestQuestionTaskStepModel info = new ExamresultsTestQuestionTaskStepModel();

            info.ID = Convert.ToInt32(dr["ID"]);
            info.ExamresultsTestQuestionTaskID = Convert.ToInt32(dr["ExamresultsTestQuestionTaskID"]);
            info.TestQuestionTaskStepID = Convert.ToInt32(dr["TestQuestionTaskStepID"]);
            info.Parameter = dr["Parameter"].ToString();
            info.StepID = int.Parse(dr["StepID"].ToString());
            if (dr["Score"] != DBNull.Value)
                info.Score = decimal.Parse(dr["Score"].ToString());

            if (!(dr["TaskType"] is DBNull))
                info.TaskType = int.Parse(dr["TaskType"].ToString());

            if (!(dr["SystemType"] is DBNull))
                info.SystemType = int.Parse(dr["SystemType"].ToString());

            if (dr["StepDescribe"] != DBNull.Value)
                info.StepDescribe = dr["StepDescribe"].ToString();

            if (!(dr["DevType"] is DBNull))
                info.DevType = int.Parse(dr["DevType"].ToString());

            if (!(dr["DevID"] is DBNull))
                info.DevID = dr["DevID"].ToString();
            if (!(dr["ScoreStandard"] is DBNull))
                info.ScoreStandard = dr["ScoreStandard"].ToString();
            else
                info.ScoreStandard = String.Empty;
            if (!(dr["StepContent"] is DBNull))
                info.StepContent = dr["StepContent"].ToString();

            info.Prerequisite = dr["Prerequisite"].ToString();

            if (dr["OperationScore"] != DBNull.Value)
                info.OperationScore = decimal.Parse(dr["OperationScore"].ToString());
            if (dr["ResultScore"] != DBNull.Value)
                info.ResultScore = decimal.Parse(dr["ResultScore"].ToString());
            if (dr["TimeScore"] != DBNull.Value)
                info.TimeScore = decimal.Parse(dr["TimeScore"].ToString());
            if (dr.Table.Columns.Contains("OperationTime") && dr["OperationTime"] != DBNull.Value)
                info.OperationTime = Convert.ToDateTime(dr["OperationTime"].ToString());

            if (dr["TotalScore"] != DBNull.Value)
                info.TotalScore = decimal.Parse(dr["TotalScore"].ToString());
            if (dr["CompositeScore"] != DBNull.Value)
                info.CompositeScore = decimal.Parse(dr["CompositeScore"].ToString());

            if (dr.Table.Columns.Contains("Proportion") && dr["Proportion"] != DBNull.Value)
                info.Proportion = decimal.Parse(dr["Proportion"].ToString());

            if (dr.Table.Columns.Contains("State") && !(dr["State"] is DBNull))
                info.State = int.Parse(dr["State"].ToString());

            if (dr.Table.Columns.Contains("ExecTimes") && !(dr["ExecTimes"] is DBNull))
                info.ExecTimes = int.Parse(dr["ExecTimes"].ToString());
            if (dr.Table.Columns.Contains("ExecState"))
                info.ExecState = dr["ExecState"] is DBNull ? 0 : int.Parse(dr["ExecState"].ToString());

            if (dr.Table.Columns.Contains("AudioFile") && !(dr["AudioFile"] is DBNull))
                info.AudioFile = (byte[])dr["AudioFile"];
            if (!(dr["Keyword"] is DBNull))
                info.Keyword = dr["Keyword"].ToString();

            if (!(dr["Order"] is DBNull))
                info.Order = int.Parse(dr["Order"].ToString());
            if (!(dr["IsRepeat"] is DBNull))
                info.IsRepeat = bool.Parse(dr["IsRepeat"].ToString());
            if (!(dr["IsCommand"] is DBNull))
                info.IsCommand = bool.Parse(dr["IsCommand"].ToString());
            if (dr.Table.Columns.Contains("BeginTime") && dr["BeginTime"] != DBNull.Value)
                info.BeginTime = Convert.ToDateTime(dr["BeginTime"].ToString());
            if (dr.Table.Columns.Contains("EndTime") && dr["EndTime"] != DBNull.Value)
                info.EndTime = Convert.ToDateTime(dr["EndTime"].ToString());
            if (dr.Table.Columns.Contains("ScoreDescribe") && dr["ScoreDescribe"] != DBNull.Value)
                info.ScoreDescribe = dr["ScoreDescribe"].ToString();

            if (dr.Table.Columns.Contains("OutRangeScore") && dr["OutRangeScore"] != DBNull.Value)
                info.OutRangeScore = decimal.Parse(dr["OutRangeScore"].ToString());
            if (dr.Table.Columns.Contains("MutexStep") && dr["MutexStep"] != DBNull.Value)
                info.MutexStep = dr["MutexStep"].ToString();

            if (dr["LimitedDuration"] != DBNull.Value)
                info.LimitedDuration = bool.Parse(dr["LimitedDuration"].ToString());
            if (dr["Deduction"] != DBNull.Value)
                info.Deduction = decimal.Parse(dr["Deduction"].ToString());
            if (dr["Duration"] != DBNull.Value)
                info.Duration = Convert.ToInt32(dr["Duration"].ToString());
            if (!(dr["IsKeyStep"] is DBNull))
                info.IsKeyStep = bool.Parse(dr["IsKeyStep"].ToString());

            if (dr["StepID1"] != DBNull.Value)
                info.StepID1 = int.Parse(dr["StepID1"].ToString());
            if (dr["StepDescribe1"] != DBNull.Value)
                info.StepDescribe1 = dr["StepDescribe1"].ToString();
            if (!(dr["DevType1"] is DBNull))
                info.DevType1 = int.Parse(dr["DevType1"].ToString());
            if (!(dr["DevID1"] is DBNull))
                info.DevID1 = dr["DevID1"].ToString();
            if (!(dr["Parameter1"] is DBNull))
                info.Parameter1 = dr["Parameter1"].ToString();
           
                if (!(dr["Estimate"] is DBNull))
                info.Estimate = bool.Parse(dr["Estimate"].ToString());
            return info;
        }

        public static void UpdateStepContent(int iD, string stepContent)
        {
            string sql = "update examresultstestquestiontaskstep set StepContent=@StepContent where ID =@ID ";

            MySqlParameter[] parms = { new MySqlParameter("@StepContent", stepContent), new MySqlParameter("@ID", iD) };
            DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static void UpdateScoreDescribe(int iD, string scoreDescribe)
        {
            string sql = "update examresultstestquestiontaskstep set ScoreDescribe=@ScoreDescribe where ID =@ID ";

            MySqlParameter[] parms = { new MySqlParameter("@ScoreDescribe", scoreDescribe), new MySqlParameter("@ID", iD) };
            DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static void UpdateOperationScore(int iD, decimal operationScore)
        {
            string sql = "update examresultstestquestiontaskstep set OperationScore=@OperationScore where ID =@ID ";

            MySqlParameter[] parms = { new MySqlParameter("@OperationScore", operationScore), new MySqlParameter("@ID", iD) };
            DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static void UpdateCompositeScore(int iD, decimal compositeScore)
        {
            string sql = "update examresultstestquestiontaskstep set CompositeScore=@CompositeScore where ID =@ID ";

            MySqlParameter[] parms = { new MySqlParameter("@CompositeScore", compositeScore), new MySqlParameter("@ID", iD) };
            DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static void UpdateAudioFile(int Id, byte[] buff)
        {
            string sql = "update examresultstestquestiontaskstep set AudioFile=@AudioFile where ID =@ID ";

            MySqlParameter[] parms = { new MySqlParameter("@AudioFile", buff), new MySqlParameter("@ID", Id) };
            DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static void UpdatePrerequisite(int Id, string  buff)
        {
            string sql = "update examresultstestquestiontaskstep set `Prerequisite`=@Prerequisite where ID =@ID ";

            MySqlParameter[] parms = { new MySqlParameter("@Prerequisite", buff), new MySqlParameter("@ID", Id) };
            DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static void Update(ExamresultsTestQuestionTaskStepModel testQuestionTaskStep)
        {
            string sql = @"
                            UPDATE `examresultstestquestiontaskstep`
                            SET
                            `ExamresultsTestQuestionTaskID` = @ExamresultsTestQuestionTaskID,
                            `StepID` = @StepID,
                            `Parameter` = @Parameter,
                            `Score` = @Score,
                            `SystemType` = @SystemType,
                            `TaskType` = @TaskType,
                            `StepDescribe` = @StepDescribe,
                            `DevType` = @DevType,
                            `DevID` = @DevID,
                            `ScoreStandard` = @ScoreStandard,
                            `OperationScore` = @OperationScore,
                            `ResultScore` = @ResultScore,
                            `TimeScore` = @TimeScore,
                            `OperationTime` = @OperationTime,
                            `Proportion` = @Proportion,
                            `TotalScore` = @TotalScore,
                            `CompositeScore` = @CompositeScore,
                            `State` = @State,
                            `ExecTimes` = @ExecTimes,
                            `StepContent` = @StepContent,
                            `Prerequisite` = @Prerequisite,
                            `ExecState` = @ExecState,
                            `Keyword` = @Keyword,
                            `TestQuestionTaskStepID` = @TestQuestionTaskStepID,
                            `Order` = @Order,
                            `IsRepeat` = @IsRepeat,
                            `BeginTime` = @BeginTime,
                            `EndTime` = @EndTime,
                            `IsCommand` = @IsCommand,
                            `ScoreDescribe` = @ScoreDescribe,
                            `IsKeyStep` = @IsKeyStep,
                            `StepID1` = @StepID1,
                            `StepDescribe1` = @StepDescribe1,
                            `DevType1` = @DevType1,
                            `DevID1` = @DevID1,
                            `Parameter1` = @Parameter1,
                            `Estimate` = @Estimate,
                            WHERE `ID` = @ID;
                        ";

            MySqlParameter[] parms = {
                new MySqlParameter("@ExamresultsTestQuestionTaskID", testQuestionTaskStep.ExamresultsTestQuestionTaskID ),
                new MySqlParameter("@StepID", testQuestionTaskStep.StepID),
                new MySqlParameter("@Parameter", testQuestionTaskStep.Parameter),
                new MySqlParameter("@Score", testQuestionTaskStep.Score),
                new MySqlParameter("@SystemType", testQuestionTaskStep.SystemType ),
                new MySqlParameter("@TaskType", testQuestionTaskStep.TaskType),
                new MySqlParameter("@StepDescribe", testQuestionTaskStep.StepDescribe),
                new MySqlParameter("@DevType", testQuestionTaskStep.DevType),
                new MySqlParameter("@DevID", testQuestionTaskStep.DevID ),
                new MySqlParameter("@ScoreStandard", testQuestionTaskStep.ScoreStandard),
                new MySqlParameter("@OperationScore", testQuestionTaskStep.OperationScore),
                new MySqlParameter("@ResultScore", testQuestionTaskStep.ResultScore),
                new MySqlParameter("@TimeScore", testQuestionTaskStep.TimeScore),
                new MySqlParameter("@OperationTime", testQuestionTaskStep.OperationTime),
                new MySqlParameter("@Proportion", testQuestionTaskStep.Proportion),
                new MySqlParameter("@TotalScore", testQuestionTaskStep.TotalScore),
                new MySqlParameter("@CompositeScore", testQuestionTaskStep.CompositeScore),
                new MySqlParameter("@State", testQuestionTaskStep.State),
                new MySqlParameter("@ExecTimes", testQuestionTaskStep.ExecTimes),
                new MySqlParameter("@StepContent", testQuestionTaskStep.StepContent),
                new MySqlParameter("@Prerequisite", testQuestionTaskStep.Prerequisite),
                new MySqlParameter("@ExecState", testQuestionTaskStep.ExecState),
                new MySqlParameter("@Keyword", testQuestionTaskStep.Keyword),
                new MySqlParameter("@TestQuestionTaskStepID", testQuestionTaskStep.TestQuestionTaskStepID),
                new MySqlParameter("@Order", testQuestionTaskStep.Order),
                new MySqlParameter("@IsRepeat", testQuestionTaskStep.IsRepeat),
                new MySqlParameter("@BeginTime", testQuestionTaskStep.BeginTime),
                new MySqlParameter("@EndTime", testQuestionTaskStep.EndTime),
                new MySqlParameter("@IsCommand", testQuestionTaskStep.IsCommand),
                new MySqlParameter("@ScoreDescribe", testQuestionTaskStep.ScoreDescribe),
                new MySqlParameter("@IsKeyStep", testQuestionTaskStep.IsKeyStep),
                 new MySqlParameter("@StepID1", testQuestionTaskStep.StepID1),
                new MySqlParameter("@StepDescribe1", testQuestionTaskStep.StepDescribe1),
                new MySqlParameter("@DevType1", testQuestionTaskStep.DevType1),
                new MySqlParameter("@DevID1", testQuestionTaskStep.DevID1),
                new MySqlParameter("@Parameter1", testQuestionTaskStep.Parameter1),
                new MySqlParameter("@Estimate", testQuestionTaskStep.Estimate),
                new MySqlParameter("@ID", testQuestionTaskStep.ID)
            };

            DbHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }
    }
}
